package org.tabularium.text.se;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInput;
import java.io.ObjectInputStream;
import java.io.ObjectOutput;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.tabularium.net.downloader.Resource;

public class SqliteRawStorer extends SqlRawStorer {

	protected Connection connection;

	protected String dbname = null;

	/*The SQL command "BEGIN TRANSACTION" (the TRANSACTION keyword is optional) is used to take SQLite out of autocommit mode. 
	 * Note that the BEGIN command does not acquire any locks on the database. After a BEGIN command, 
	 * a SHARED lock will be acquired when the first SELECT statement is executed. 
	 * A RESERVED lock will be acquired when the first INSERT, UPDATE, or DELETE statement is executed. 
	 * No EXCLUSIVE lock is acquired until either the memory cache fills up and must be spilled to disk or until the transaction commits. 
	 * In this way, the system delays blocking read access to the file file until the last possible moment.
	 * If the SQL COMMIT command turns autocommit on and the autocommit logic then tries to commit change but fails 
	 * because some other process is holding a SHARED lock, then autocommit is turned back off automatically. 
	 * This allows the user to retry the COMMIT at a later time after the SHARED lock has had an opportunity to clear.*/
	//	protected static final String readLock = "LOCK TABLES map READ;";
	//	protected static final String writeLock = "LOCK TABLES map WRITE;";
	//	protected static final String unlock = "UNLOCK TABLES;";

	protected static final String mapTable = "CREATE TABLE map ("
			+ "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
			"kkey BLOB NOT NULL, " +
			"value BLOB NOT NULL, " +
			"ts BIGINT NOT NULL);";

	protected static final String kkeyIndex = "CREATE INDEX kkey_idx ON map (kkey);";

	protected static final String tsIndex = "CREATE INDEX ts_ids ON map (ts);";

	protected static final String getValue = "SELECT value FROM map WHERE kkey=?;";

	protected static final String getValueWTimestamp = "SELECT value FROM map WHERE kkey=? AND ts=?;";

	protected static final String addValue = "INSERT INTO map (kkey, value, ts) VALUES (?,?,?);";

	protected static final String reset = "DELETE FROM map;";

	protected static final String getKeys = "SELECT kkey FROM map;";

	protected static final String getKey = "SELECT kkey FROM map WHERE id=?;";

	protected static final String getTimestamp = "SELECT ts FROM map WHERE kkey=?;";

	protected static final String getTimestampById = "SELECT ts FROM map WHERE id=?;";

	protected static final String getIds = "SELECT id FROM map;";

	protected static final String removeValue = "DELETE FROM map WHERE kkey=?;";

	protected static final String removeValueWTimestamp = "DELETE FROM map WHERE kkey=? AND ts=?;";

	protected static final String numValues = "SELECT COUNT(*) FROM map;";

//	public void init (String driver, String url, String creationUrl, String dbname, boolean reset) throws ClassNotFoundException,
//	SQLException {
//		this.dbname = dbname;
//
//		PreparedStatement stat;
//
//		if ((driver == null) || (url == null)) {
//			throw new IllegalArgumentException();
//		}
//
//		try {
//			Class.forName(driver).newInstance();
//		} catch (Exception ex) {
//			// IllegalAccessException and InstantiationException exceptions
//			throw new ClassNotFoundException("Unable to instantiate " + driver
//					+ " class");
//		}
//		
//		try {
//			//getConnection with Sqlite creates database if does not exist (in the main directory of the project)
//			connection = DriverManager.getConnection(url + ":" + dbname);
//			stat = connection.prepareStatement(mapTable);
//			stat.execute();
//			stat.close();
//			stat = connection.prepareStatement(kkeyIndex);
//			stat.execute();
//			stat.close();
//			stat = connection.prepareStatement(tsIndex);
//			stat.execute();
//			stat.close();
//		} catch (SQLException ex) {
//			if (ex.getErrorCode() == 0) {	// table already exist
//				return;
//			} else {
//				throw ex;
//			}
//		}
//	}


	@Override
	public void init (String driver, String url, String creationUrl, String dbname, boolean reset) throws ClassNotFoundException,
	SQLException {
		this.dbname = dbname;
		PreparedStatement stat;

		if ((driver == null) || (url == null)) {
			throw new IllegalArgumentException();
		}
		if (reset) {			
			File fn = new File(dbname);
			if (fn.exists())
				fn.delete();
		}

		try {
			Class.forName(driver).newInstance();
		} catch (Exception ex) {
			// IllegalAccessException and InstantiationException exceptions
			throw new ClassNotFoundException("Unable to instantiate " + driver
					+ " class");
		}
		try {
			connection = DriverManager.getConnection(url + ":" + dbname);
		} catch (SQLException ex) {
			if (ex.getErrorCode() == 1049) { // unknown database
				reset = true;
			} else {
				throw ex;
			}
		}

		if (reset) {			
			createDb(url + ":" + dbname);
		}
//		stat = connection.prepareStatement("USE " + dbname + ";");
//		stat.execute();
//		stat.close();		
	}

	
	/**
	 * If you want override this method, just call it and than call
	 * prepareStatement to create further tables.
	 * 
	 * @param url
	 * @throws SQLException
	 */
	protected void createDb(String url) throws SQLException {
		PreparedStatement stat;
		stat = connection.prepareStatement(mapTable);
		stat.execute();
		stat.close();
		stat = connection.prepareStatement(kkeyIndex);
		stat.execute();
		stat.close();
		stat = connection.prepareStatement(tsIndex);
		stat.execute();
		stat.close();

//		
//
//		PreparedStatement stat;
//		stat = connection.prepareStatement("DROP DATABASE IF EXISTS " + dbname
//				+ ";");
//		stat.execute();
//		stat.close();
//		stat = connection.prepareStatement("CREATE DATABASE " + dbname + ";");
//		stat.execute();
//		stat.close();
//		stat = connection.prepareStatement("USE " + dbname + ";");
//		stat.execute();
//		stat.close();
//		stat = connection.prepareStatement(mapTable);
//		stat.execute();
//		stat.close();
	}
	
	@Override
	public void store(String id, byte[] obj) throws IOException {
		store(id, obj, System.currentTimeMillis());
	}

	@Override
	public void store(Resource res) throws IOException {				
		store(res.getURL(), RawStorer.serialize(res));
	}

	@Override
	public List<byte[]> retrieve(String id) throws IOException {
		List<byte[]> ret = new ArrayList<byte[]>();
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(getValue);
			stat.setString(1, id);
			rs = stat.executeQuery();
			while (rs.next()) {
				ret.add(rs.getBytes(1));
			}
			rs.close();
			stat.close();
		} catch (Exception ex) {
			throw new IOException("Sql exception: " + ex.toString());
		}
		return ret;
	}

	@Override
	public List<Resource> retrieveResource(String id) throws IOException {
		List<byte[]> rets = retrieve(id);		
		List<Resource> ret = new ArrayList<Resource>();
		Iterator<byte[]> iter = rets.iterator();
		while (iter.hasNext()) {
			byte[] bb = (byte[]) iter.next();
			try {
				ret.add((Resource)RawStorer.deserialize(bb));
			} catch (ClassNotFoundException e) {
				throw new IOException("Serialization exception: " + e.toString());
			}
		}
		return ret;
	}

	@Override
	public List<Long> timestamp(String key) throws IOException {
		List<Long> ret = new ArrayList<Long>();
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(getTimestamp);
			stat.setString(1, key);
			rs = stat.executeQuery();
			while (rs.next()) {
				ret.add(rs.getLong(1));
			}
			rs.close();
			stat.close();
		} catch (Exception ex) {
			throw new IOException("Sql exception: " + ex.toString());
		}
		return ret;
	}

	@Override
	public void remove(String id) throws IOException {
		try {
			PreparedStatement stat;
			stat = connection.prepareStatement(removeValue);
			stat.setString(1, id);
			stat.executeUpdate();
			stat.close();
		} catch (Exception ex) {
			throw new IOException("Sql exception: " + ex.toString());
		}
	}

	@Override
	public DocIterator browse() throws IOException {
		int ids[] = new int[0];
		try {
			ResultSet rs;
			int dim = 0;
			PreparedStatement stat;
			// instead of going through a dynamic list,
			// get the size of the result set and create a static array
			stat = connection.prepareStatement(numValues);
			rs = stat.executeQuery();
			if (rs.next()) {
				dim = rs.getInt(1);
			}
			rs.close();
			stat.close();
			ids = new int[dim];
			stat = connection.prepareStatement(getIds);
			rs = stat.executeQuery();
			for (int i = 0; rs.next(); i++) {
				ids[i] = rs.getInt(1);
			}
			rs.close();
			stat.close();
		} catch (Exception ex) {
			throw new IOException("Sql exception: " + ex.toString());
		}
		return new SqlRawStorerIterator(this, ids);
	}

	String getKeyById(int id) throws IOException {
		String key = null;
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(getKey);
			stat.setInt(1, id);
			rs = stat.executeQuery();
			if (rs.next()) {
				key = rs.getString(1);
			}
			rs.close();
			stat.close();
		} catch (Exception ex) {
			throw new IOException("Sql exception: " + ex.toString());
		}
		return key;
	}

	long getTimestampById(int id) throws IOException {
		long ts = 0;
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(getTimestampById);
			stat.setInt(1, id);
			rs = stat.executeQuery();
			if (rs.next()) {
				ts = rs.getLong(1);
			}
			rs.close();
			stat.close();
		} catch (Exception ex) {
			throw new IOException("Sql exception: " + ex.toString());
		}
		return ts;
	}

	@Override
	public int size() throws IOException {
		int n = -1;
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(numValues);
			rs = stat.executeQuery();
			if (rs.next()) {
				n = rs.getInt(1);
			}
			rs.close();
			stat.close();
		} catch (Exception ex) {
			throw new IOException("Sql exception: " + ex.toString());
		}
		return n;
	}

	@Override
	public void clear() throws IOException {
		try {
			PreparedStatement stat;
			stat = connection.prepareStatement(reset);
			stat.executeUpdate();
			stat.close();
		} catch (Exception ex) {
			throw new IOException("Sql exception: " + ex.toString());
		}
	}


	@Override
	public byte[] retrieve(String id, long timestamp) throws IOException {
		byte[] value = null;
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(getValueWTimestamp);
			stat.setString(1, id);
			stat.setLong(2, timestamp);
			rs = stat.executeQuery();
			if (rs.next()) {
				value = rs.getBytes(1);
			}
			rs.close();
			stat.close();
		} catch (Exception ex) {
			throw new IOException("Sql exception: " + ex.toString());
		}
		return value;
	}

	@Override
	public void store(String id, byte[] obj, long timestamp) throws IOException {
		try {
			ResultSet rs;
			PreparedStatement stat;
			// instead of going through a dynamic list,
			// get the size of the result set and create a static array
			stat = connection.prepareStatement(addValue);
			stat.setString(1, id);
			InputStream is = new ByteArrayInputStream(obj);
			stat.setBytes(2, obj);
			stat.setLong(3, timestamp);
			stat.execute();
			stat.close();
			is.close();
		} catch (Exception ex) {
			throw new IOException("Sql exception: " + ex.toString());
		}
	}

	@Override
	public Resource retrieveResource(String id, long timestamp)
			throws IOException {
		byte[] bb = this.retrieve(id, timestamp);
		Resource res = null;
		try {
			res = (Resource)RawStorer.deserialize(bb);
		} catch (ClassNotFoundException e) {
			throw new IOException("Serialization exception: " + e.toString());
		}
		return res;
	}

	@Override
	public void remove(String id, long timestamp) throws IOException {
		try {
			PreparedStatement stat;
			stat = connection.prepareStatement(this.removeValueWTimestamp);
			stat.setString(1, id);
			stat.setLong(2, timestamp);
			stat.executeUpdate();
			stat.close();
		} catch (Exception ex) {
			throw new IOException("Sql exception: " + ex.toString());
		}
	}

	public static void main(String[] args){ 
		SqliteRawStorer q = null;
		try {
			List<byte[]> yourBytes = new ArrayList<byte[]>();
			Long l;
			q = new SqliteRawStorer();
			q.init("org.sqlite.JDBC", "jdbc:sqlite", null, "test.db", true);
			for (int i = 0; i < 10; i++) {
				l = new Long((long) (Math.random() * 1000));
				if (q.retrieve(Long.toString(l)) != null) {
					System.out.println("skip key " + l + " already stored");
					continue;
				}
				ByteArrayOutputStream bos = new ByteArrayOutputStream();
				ObjectOutput out = null;
				try {
					out = new ObjectOutputStream(bos);
					out.writeObject(l);
					yourBytes.add(bos.toByteArray());
				} finally {
					try {
						if (out != null) {
							out.close();
						}
					} catch (IOException ex) {
						// ignore close exception
					}
					try {
						bos.close();
					} catch (IOException ex) {
						// ignore close exception
					}
				}

				System.out.println("store " + l.toString() + ":" + yourBytes.get(i)
						+ " " + l.toString());
				q.store(Long.toString(l), yourBytes.get(i));
				System.out.println("size " + q.size());
			}

			DocIterator iter = q.browse();
			while (iter.hasNext()) {
				String key = (String) iter.next();
				yourBytes = q.retrieve(key);
				for(byte[] b : yourBytes){
					ByteArrayInputStream bis = new ByteArrayInputStream(b);
					ObjectInput in = null;
					try {
						in = new ObjectInputStream(bis);
						l = (Long) in.readObject();
						System.out.println("read " + l + " " + b + " "
								+ l.toString());

					} finally {
						try {
							bis.close();
						} catch (IOException ex) {
							// ignore close exception
						}
						try {
							if (in != null) {
								in.close();
							}
						} catch (IOException ex) {
							// ignore close exception
						}
					}
				}
			}

			iter = q.browse();
			while (iter.hasNext()) {
				String key = (String) iter.next();
				yourBytes = q.retrieve(key);
				System.out.println("delete: " + key);
				q.remove(key);
				System.out.println("size: " + q.size());
				for(byte[] b : yourBytes){
					ByteArrayInputStream bis = new ByteArrayInputStream(b);
					ObjectInput in = null;
					try {
						in = new ObjectInputStream(bis);
						l = (Long) in.readObject();
						System.out.println("read " + l + " " + b + " "
								+ l.toString());

					} finally {
						try {
							bis.close();
						} catch (IOException ex) {
							// ignore close exception
						}
						try {
							if (in != null) {
								in.close();
							}
						} catch (IOException ex) {
							// ignore close exception
						}
					}
				}
			}

		} catch (Exception ex) {
			System.out.println(ex.toString());
			ex.printStackTrace();
		}
	}
} 